﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using DeqiYQ.SQLConn;
using Newtonsoft.Json.Linq;
using System.Runtime.Serialization;
using System.Web.Script.Serialization;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;

namespace DeqiYQ.Controllers
{
    public class PanelController : Controller
    {
        BaseDao dao = new BaseDao();
        // GET: Panel

        
        
        public ActionResult Index()
        {
            if (Request.Cookies["UserId"] == null)
            {
                return RedirectToAction("LoginOut");
            }
            string UserName = dao.GetString("t_d_User", "UserName", "and UserId=" + Request.Cookies["UserId"].Value);
            ViewBag.UserName = UserName;
            string Authority = dao.GetString("t_d_User", "Authority", "and UserId=" + Request.Cookies["UserId"].Value);
            ViewBag.Authority = Authority;
            if (Authority == "2")
            {
                return RedirectToAction("LoginOut");
            }
            return View("Index");
        }

        public ActionResult Login()
        {
            return View("Login");
        }

        public ActionResult LoginOut()
        {
            //删除cookies
            HttpCookie aCookie;
            string cookieName;
            int limit = Request.Cookies.Count;
            for (int i = 0; i < limit; i++)
            {
                cookieName = Request.Cookies[i].Name;
                aCookie = new HttpCookie(cookieName);
                aCookie.Expires = DateTime.Now.AddDays(-1);
                Response.Cookies.Add(aCookie);
            }
            return View("Login");
        }


        public  ActionResult UserView()
        {
            
            return View("User");
        }

        public ActionResult UserPassView()
        {
            return View("UserPassWord");
        }

        public ActionResult UserPassChange()
        {
            string OldPass= Request.Form["OldPass"] == null ? null : Request.Form["OldPass"].ToString();
            string NewPass = Request.Form["NewPass"] == null ? null : Request.Form["NewPass"].ToString();
            string UserId = Request.Cookies["UserId"].Value;

            Dictionary<string, object> result = new Dictionary<string, object>();
 
            try
            {
                string TempPass = dao.GetString("t_d_User", "PassWord", "and UserId=" + UserId);
                if (TempPass == OldPass)
                {
                    string sql = "update t_d_User set PassWord='" + NewPass + "' where UserId=" + UserId;
                    BaseDao.execute(sql);
                    result.Add("result", "OK");
                    return Json(result);
                }
                else
                {
                    result.Add("result", "原始密码错误");
                    return Json(result);
                }
            }
            catch (Exception e)
            {
                Console.Write(e.Message.ToString());
                result.Add("result", "error:" + e.Message.ToString().Replace("'", ""));
                return Json(result);
            }




        }


        public ActionResult UserAdd()
        {
            var sr = new StreamReader(Request.InputStream);
            var stream = sr.ReadToEnd();
            JObject obj = JObject.Parse(stream);
            sr.Close();
            Dictionary<string, object> result = new Dictionary<string, object>();

            try
            {
                Dictionary<string, object> map = new Dictionary<string, object>();

                foreach (var item in obj)
                {
                    map.Add(item.Key, item.Value);
                }
                map["IsDelete"] = "0";
                map["PassWord"] = "12345678";
                if(""!=dao.GetString("t_d_User","UserName","and UserName='" + map["UserName"]+"'"))
                {
                    result.Add("result", "用户名已存在");
                    return Json(result);
                }
                dao.save("t_d_User", map);
                result.Add("result", "OK");
                return Json(result);
            }
            catch (Exception e)
            {
                Console.Write(e.Message.ToString());
                result.Add("result", "error:" + e.Message.ToString().Replace("'", ""));
                return Json(result);
            }
        }


        public ActionResult UserTableRequest()
        {
            int page = Int32.Parse(Request.Form["page"]);
            int limit = Int32.Parse(Request.Form["limit"]);
            string condition = Request.Form["condition"].ToString();
            string SortName = Request.Form["SortName"].ToString();
            string Order = Request.Form["Order"].ToString();

            try
            {
                //以下是分页查询语句
                string sql = "", SqlNum = "";
                sql = " select top " + limit.ToString() + " * from " +
                        " ( " +
                        " SELECT ROW_NUMBER() OVER (order by " + SortName + " " + Order + ") as rowNumber,UserName,NickName,UserId,Authority" +
                        "" +
                        " FROM t_d_User" +
                        " where 1=1 " + condition +
                        " ) as s " +
                        " where rowNumber>" + (limit * (page - 1)).ToString();

                List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
                list = dao.GetList(sql, new string[] { "UserName", "NickName", "UserId", "Authority" });
                SqlNum = "select count(*) as TotalNum from t_d_User where 1=1 " + condition;
                List<Dictionary<string, object>> ListNum = new List<Dictionary<string, object>>();
                ListNum = dao.GetList(SqlNum, new string[] { "TotalNum" });

                TableData td = new TableData();
                td.code = "";
                td.msg = "";
                td.count = int.Parse(ListNum[0]["TotalNum"].ToString());
                td.data = list;
                return Json(td);
            }
            catch (Exception ex)
            {
                Dictionary<string, object> datalog = new Dictionary<string, object>();
                datalog.Add("type", int.Parse("1"));
                datalog.Add("OpTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                datalog.Add("OpID", int.Parse("1"));
                datalog.Add("OpLog", "异常：加载需求管理表格异常 /PanelController/UserTableRequest:" + ex.Message.ToString().Replace("'", ""));
                dao.save("T_LOG", datalog);
                return Json('0');
            }

        }


        public ActionResult GetUserById()
        {
            string UserId = Request.Form["UserId"] == null ? null : Request.Form["UserId"].ToString();

            Dictionary<string, object> result = new Dictionary<string, object>();
            string sql = "";
            sql = "select UserName,PassWord,NickName,UserId,Authority from t_d_User where UserId=" + UserId;
            try
            {
                var map = dao.GetList(sql, new string[] { "UserName", "PassWord", "NickName", "UserId", "Authority" })[0];
                return Json(map);
            }
            catch (Exception e)
            {
                Console.Write(e.Message.ToString());
                result.Add("result", "error:" + e.Message.ToString().Replace("'", ""));
                return Json(result);

            }

        }

        public ActionResult SaveUserById()
        {
            var sr = new StreamReader(Request.InputStream);
            var stream = sr.ReadToEnd();
            JObject obj = JObject.Parse(stream);
            sr.Close();
            Dictionary<string, object> result = new Dictionary<string, object>();

            try
            {
                Dictionary<string, object> map = new Dictionary<string, object>();

                foreach (var item in obj)
                {
                    map.Add(item.Key, item.Value);
                }
                string UserId = map["UserId"].ToString();
                map.Remove("UserId");
                dao.update("t_d_User", map,"UserId="+UserId);
                result.Add("result", "OK");
                return Json(result);
            }
            catch (Exception e)
            {
                Console.Write(e.Message.ToString());
                result.Add("result", "error:" + e.Message.ToString().Replace("'", ""));
                return Json(result);
            }
        }




        public ActionResult UserResetPass()
        {
            string UserId = Request.Form["UserId"] == null ? null : Request.Form["UserId"].ToString();

            Dictionary<string, object> result = new Dictionary<string, object>();
            try
            {
                Dictionary<string, object> map = new Dictionary<string, object>
                {
                    ["PassWord"]="12345678"
                };
                dao.update("t_d_User", map, "UserId=" + UserId);
                result.Add("result", "OK");
                return Json(result);

            }
            catch (Exception e)
            {
                Console.Write(e.Message.ToString());
                result.Add("result", "error:" + e.Message.ToString().Replace("'", ""));
                return Json(result);
            }


        }

        public ActionResult UserDeleteById()
        {
            string UserId = Request.Form["UserId"] == null ? null : Request.Form["UserId"].ToString();

            Dictionary<string, object> result = new Dictionary<string, object>();
            try
            {
                Dictionary<string, object> map = new Dictionary<string, object>
                {
                    ["IsDelete"] = "1"
                };
                dao.update("t_d_User", map, "UserId=" + UserId);
                result.Add("result", "OK");
                return Json(result);

            }
            catch (Exception e)
            {
                Console.Write(e.Message.ToString());
                result.Add("result", "error:" + e.Message.ToString().Replace("'", ""));
                return Json(result);
            }
        }

/////////////////////////////////////////////////////////////////////////////////////////////
///设备管理
        public ActionResult DeviceView()
        {
            return View("Device");
        }


        public ActionResult DeviceTableRequest()
        {
            int page = Int32.Parse(Request.Form["page"]);
            int limit = Int32.Parse(Request.Form["limit"]);
            string condition = Request.Form["condition"].ToString();
            string SortName = Request.Form["SortName"].ToString();
            string Order = Request.Form["Order"].ToString();

            try
            {
                //以下是分页查询语句
                string sql = "", SqlNum = "";
                sql = " select top " + limit.ToString() + " * from " +
                        " ( " +
                        " SELECT ROW_NUMBER() OVER (order by " + SortName + " " + Order + ") as rowNumber,DeviceId,MN,Name,Address," +
                        " lon,lat,ChannelNum" +
                        " FROM t_d_Device" +
                        " where 1=1 " + condition +
                        " ) as s " +
                        " where rowNumber>" + (limit * (page - 1)).ToString();

                List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
                list = dao.GetList(sql, new string[] { "DeviceId", "MN", "Name", "Address","lon", "lat", "ChannelNum" });
                SqlNum = "select count(*) as TotalNum from t_d_Device where 1=1 " + condition;
                List<Dictionary<string, object>> ListNum = new List<Dictionary<string, object>>();
                ListNum = dao.GetList(SqlNum, new string[] { "TotalNum" });

                TableData td = new TableData();
                td.code = "";
                td.msg = "";
                td.count = int.Parse(ListNum[0]["TotalNum"].ToString());
                td.data = list;
                return Json(td);
            }
            catch (Exception ex)
            {
                Dictionary<string, object> datalog = new Dictionary<string, object>();
                datalog.Add("type", int.Parse("1"));
                datalog.Add("OpTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                datalog.Add("OpID", int.Parse("1"));
                datalog.Add("OpLog", "异常：加载需求管理表格异常 /PanelController/UserTableRequest:" + ex.Message.ToString().Replace("'", ""));
                dao.save("T_LOG", datalog);
                return Json('0');
            }

        }


        public ActionResult DeviceAdd()
        {
            var sr = new StreamReader(Request.InputStream);
            var stream = sr.ReadToEnd();
            JObject obj = JObject.Parse(stream);
            sr.Close();
            Dictionary<string, object> result = new Dictionary<string, object>();

            try
            {
                Dictionary<string, object> map = new Dictionary<string, object>();

                foreach (var item in obj)
                {
                    map.Add(item.Key, item.Value);
                }
                map["IsDelete"] = "0";
                if ("" != dao.GetString("t_d_Device", "MN", "and MN='" + map["MN"] + "'"))
                {
                    result.Add("result", "MN已存在");
                    return Json(result);
                }
                dao.save("t_d_Device", map);
                result.Add("result", "OK");
                return Json(result);
            }
            catch (Exception e)
            {
                Console.Write(e.Message.ToString());
                result.Add("result", "error:" + e.Message.ToString().Replace("'", ""));
                return Json(result);
            }
        }


        public ActionResult GetDeviceById()
        {
            string DeviceId = Request.Form["DeviceId"] == null ? null : Request.Form["DeviceId"].ToString();

            Dictionary<string, object> result = new Dictionary<string, object>();
            string sql = "";
            sql = "select DeviceId,MN,Name,Address,lon,lat,ChannelNum from t_d_Device where DeviceId=" + DeviceId;
            try
            {
                var map = dao.GetList(sql, new string[] { "DeviceId", "MN", "Name", "Address", "lon", "lat", "ChannelNum" })[0];
                return Json(map);
            }
            catch (Exception e)
            {
                Console.Write(e.Message.ToString());
                result.Add("result", "error:" + e.Message.ToString().Replace("'", ""));
                return Json(result);

            }

        }

        public ActionResult SaveDeviceById()
        {
            var sr = new StreamReader(Request.InputStream);
            var stream = sr.ReadToEnd();
            JObject obj = JObject.Parse(stream);
            sr.Close();
            Dictionary<string, object> result = new Dictionary<string, object>();

            try
            {
                Dictionary<string, object> map = new Dictionary<string, object>();

                foreach (var item in obj)
                {
                    map.Add(item.Key, item.Value);
                }
                string DeviceId = map["DeviceId"].ToString();
                map.Remove("DeviceId");
                dao.update("t_d_Device", map, "DeviceId=" + DeviceId);
                result.Add("result", "OK");
                return Json(result);
            }
            catch (Exception e)
            {
                Console.Write(e.Message.ToString());
                result.Add("result", "error:" + e.Message.ToString().Replace("'", ""));
                return Json(result);
            }
        }

        public ActionResult DeviceDeleteById()
        {
            string DeviceId = Request.Form["DeviceId"] == null ? null : Request.Form["DeviceId"].ToString();

            Dictionary<string, object> result = new Dictionary<string, object>();
            try
            {
                Dictionary<string, object> map = new Dictionary<string, object>
                {
                    ["IsDelete"] = "1"
                };
                dao.update("t_d_Device", map, "DeviceId=" + DeviceId);
                result.Add("result", "OK");
                return Json(result);
            }
            catch (Exception e)
            {
                Console.Write(e.Message.ToString());
                result.Add("result", "error:" + e.Message.ToString().Replace("'", ""));
                return Json(result);
            }
        }


//////////////////////////////////////////////////////////////////////////////////////////////////
///用户，设备绑定
        public ActionResult UserDeviceView()
        {
            string sql = "select * from t_d_User where IsDelete<>1 and Authority=2";
            var UserList = dao.GetList(sql, new string[] { "UserId", "Authority", "UserName", "NickName" });
            ViewBag.UserList = UserList;
            return View("UserDevice");

        }


        public ActionResult UserDeviceTableRequest()
        {
            int page = Int32.Parse(Request.Form["page"]);
            int limit = Int32.Parse(Request.Form["limit"]);
            string condition = Request.Form["condition"].ToString();
            string SortName = Request.Form["SortName"].ToString();
            string Order = Request.Form["Order"].ToString();

            try
            {
                //以下是分页查询语句
                string sql = "", SqlNum = "";
                sql = " select top " + limit.ToString() + " * from " +
                        " ( " +
                        " SELECT ROW_NUMBER() OVER (order by " + SortName + " " + Order + ") as rowNumber,b.Name,B.Address,b.DeviceId" +
                        " " +
                        " FROM t_o_UserDevice a INNER JOIN t_d_Device b ON a.DeviceId=b.DeviceId INNER JOIN t_d_User c ON a.UserId=c.UserId" +
                        " where 1=1 " + condition +
                        " ) as s " +
                        " where rowNumber>" + (limit * (page - 1)).ToString();

                List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
                list = dao.GetList(sql, new string[] { "Name", "Address", "DeviceId" });
                SqlNum = "select count(*) as TotalNum FROM t_o_UserDevice a INNER JOIN t_d_Device b ON a.DeviceId=b.DeviceId INNER JOIN t_d_User c ON a.UserId=c.UserId where 1=1 " + condition;
                List<Dictionary<string, object>> ListNum = new List<Dictionary<string, object>>();
                ListNum = dao.GetList(SqlNum, new string[] { "TotalNum" });

                TableData td = new TableData();
                td.code = "";
                td.msg = "";
                td.count = int.Parse(ListNum[0]["TotalNum"].ToString());
                td.data = list;
                return Json(td);
            }
            catch (Exception ex)
            {
                Dictionary<string, object> datalog = new Dictionary<string, object>();
                datalog.Add("type", int.Parse("1"));
                datalog.Add("OpTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                datalog.Add("OpID", int.Parse("1"));
                datalog.Add("OpLog", "异常：加载需求管理表格异常 /PanelController/UserDeviceTableRequest:" + ex.Message.ToString().Replace("'", ""));
                dao.save("T_LOG", datalog);
                return Json('0');
            }

        }


        public ActionResult UserDeviceAdd()
        {
            string DeviceId = Request.Form["DeviceId"] == null ? null : Request.Form["DeviceId"].ToString();
            string UserId = Request.Form["UserId"] == null ? null : Request.Form["UserId"].ToString();


            Dictionary<string, object> result = new Dictionary<string, object>();
            try
            {
                //string sql = "select * from t_o_UserDevice where UserId=" + UserId + " and DeviceId=" + DeviceId;
                if(""!=dao.GetString("t_o_UserDevice","UserId","and UserId=" + UserId + " and DeviceId=" + DeviceId))
                {
                    result.Add("result", "用户已添加该设备");
                    return Json(result);
                }

                Dictionary<string, object> map = new Dictionary<string, object>()
                {
                    ["DeviceId"]= DeviceId,
                    ["UserId"]= UserId
                };
                dao.save("t_o_UserDevice", map);
                result.Add("result", "OK");
                return Json(result);
            }
            catch (Exception e)
            {
                Console.Write(e.Message.ToString());
                result.Add("result", "error:" + e.Message.ToString().Replace("'", ""));
                return Json(result);
            }
        }


        public ActionResult UserDeviceDelete()
        {
            string DeviceId = Request.Form["DeviceId"] == null ? null : Request.Form["DeviceId"].ToString();
            string UserId = Request.Form["UserId"] == null ? null : Request.Form["UserId"].ToString();


            Dictionary<string, object> result = new Dictionary<string, object>();
            try
            {
                string sql = "delete t_o_UserDevice where DeviceId=" + DeviceId + " and UserId=" + UserId;
                BaseDao.execute(sql);
                result.Add("result", "OK");
                return Json(result);
            }
            catch (Exception e)
            {
                Console.Write(e.Message.ToString());
                result.Add("result", "error:" + e.Message.ToString().Replace("'", ""));
                return Json(result);
            }
        }

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////
///数据导出
        public ActionResult DataExportView()
        {
            return View("DataExport");


        }


        public ActionResult DataExportTableRequest()
        {
            int page = Int32.Parse(Request.Form["page"]);
            int limit = Int32.Parse(Request.Form["limit"]);
            string condition = Request.Form["condition"].ToString();
            string SortName = Request.Form["SortName"].ToString();
            string Order = Request.Form["Order"].ToString();

            try
            {
                //以下是分页查询语句
                string sql = "", SqlNum = "";
                sql = " select top " + limit.ToString() + " * from " +
                        " ( " +
                        " SELECT ROW_NUMBER() OVER (order by " + SortName + " " + Order + ") as rowNumber,a.*,CONVERT(varchar,a.DataTime,120) as LastTime,Name,Address" +
                        " " +
                        " FROM t_o_dataTemp a INNER JOIN t_d_Device b ON a.MN=b.MN" +
                        " where 1=1 " + condition +
                        " ) as s " +
                        " where rowNumber>" + (limit * (page - 1)).ToString();

                List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
                list = dao.GetList(sql, new string[] { "Name", "Address", "MN","LastTime","Y1", "V1", "K1", "Y2", "V2", "K2","amp1","amp2" });
                SqlNum = "select count(*) as TotalNum FROM t_o_dataTemp a INNER JOIN t_d_Device b ON a.MN=b.MN where 1=1 " + condition;
                List<Dictionary<string, object>> ListNum = new List<Dictionary<string, object>>();
                ListNum = dao.GetList(SqlNum, new string[] { "TotalNum" });

                TableData td = new TableData();
                td.code = "";
                td.msg = "";
                td.count = int.Parse(ListNum[0]["TotalNum"].ToString());
                td.data = list;
                return Json(td);
            }
            catch (Exception ex)
            {
                Dictionary<string, object> datalog = new Dictionary<string, object>();
                datalog.Add("type", int.Parse("1"));
                datalog.Add("OpTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                datalog.Add("OpID", int.Parse("1"));
                datalog.Add("OpLog", "异常：加载需求管理表格异常 /PanelController/UserDeviceTableRequest:" + ex.Message.ToString().Replace("'", ""));
                dao.save("T_LOG", datalog);
                return Json('0');
            }

        }


        public ActionResult Data2Excel()
        {
            string TimeStart = Request.Form["TimeStart"] == null ? null : Request.Form["TimeStart"].ToString().Replace(" ", "");
            string TimeEnd = Request.Form["TimeEnd"] == null ? null : Request.Form["TimeEnd"].ToString().Replace(" ", "");
            string MN = Request.Form["MN"] == null ? null : Request.Form["MN"].ToString().Replace(" ", "");

            ExcelTool export = new ExcelTool();
            XSSFWorkbook book = export.DeviceDataSheet(TimeStart+" 00:00:00", TimeEnd + " 23:59:59", MN);
            var ms = new NpoiMemoryStream(); 
            ms.AllowClose = false;
            book.Write(ms);
            ms.Flush();
            ms.Seek(0, SeekOrigin.Begin);
            return File(ms, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "导出数据" + ".xlsx");

        }

////////////////////////////////////////////////////////////////////////////////////////////////////////
///地图显示
        public ActionResult MapView()
        {
            string Sql = "";
            Sql = "select a.DeviceId,a.MN,a.Name,a.Address,a.ChannelNum,a.lon,a.lat,b.Y1,b.V1,b.K1,b.Y2,b.V2,b.K2,CONVERT(varchar,b.DataTime,120) as LastTime from t_d_Device a left join t_o_dataTemp b on a.mn=b.mn " +
                " where a.IsDelete=0";
            List<Dictionary<string, object>> DeviceData = new List<Dictionary<string, object>>();
            DeviceData = dao.GetList(Sql, new string[] { "DeviceId", "MN", "Name", "Address","ChannelNum", "lon", "lat", "Y1", "V1", "K1","Y2","V2","K2","LastTime" });
            ViewBag.DeviceDataJson = Newtonsoft.Json.JsonConvert.SerializeObject(DeviceData);
            return View("Map");
     
        }

        public ActionResult SearchDeviceByName()
        {
            string Name = Request.Form["Name"] == null ? null : Request.Form["Name"].ToString();

            Dictionary<string, object> result = new Dictionary<string, object>();
            try
            {
                string Sql = "";
                if (Name == "")
                {
                    Sql = "select a.DeviceId,a.MN,a.Name,a.Address,a.ChannelNum,a.lon,a.lat,b.Y1,b.V1,b.K1,b.Y2,b.V2,b.K2,CONVERT(varchar,b.DataTime,120) as LastTime from t_d_Device a left join t_o_dataTemp b on a.mn=b.mn " +
                        " where a.IsDelete=0";
                }
                else
                {
                    Sql = "select a.DeviceId,a.MN,a.Name,a.Address,a.ChannelNum,a.lon,a.lat,b.Y1,b.V1,b.K1,b.Y2,b.V2,b.K2,CONVERT(varchar,b.DataTime,120) as LastTime from t_d_Device a left join t_o_dataTemp b on a.mn=b.mn " +
                        " where a.IsDelete=0 and a.Name like '%"+Name+"%'";
                }

                List<Dictionary<string, object>> DeviceData = new List<Dictionary<string, object>>();
                DeviceData = dao.GetList(Sql, new string[] { "DeviceId", "MN", "Name", "Address", "ChannelNum", "lon", "lat", "Y1", "V1", "K1", "Y2", "V2", "K2", "LastTime" });
                return Json(DeviceData);
            }
            catch (Exception e)
            {
                Console.Write(e.Message.ToString());
                result.Add("result", "error:" + e.Message.ToString().Replace("'", ""));
                return Json(result);
            }


        }

///////////////////////////////////////////////////////////////////////////////////////////////////////////////
///超标页面
        public ActionResult ExcessView()
        {
            return View("Excess");
        }


        public ActionResult ExcessTableRequest()
        {
            int page = Int32.Parse(Request.Form["page"]);
            int limit = Int32.Parse(Request.Form["limit"]);
            string condition = Request.Form["condition"].ToString();
            string SortName = Request.Form["SortName"].ToString();
            string Order = Request.Form["Order"].ToString();

            try
            {
                //以下是分页查询语句
                string sql = "", SqlNum = "";
                sql = " select top " + limit.ToString() + " * from " +
                        " ( " +
                        " SELECT ROW_NUMBER() OVER (order by " + SortName + " " + Order + ") as rowNumber,CONVERT(varchar,DataTime,120) as DataTime,Name,Address," +
                        " DeviceId,Y1,V1,K1,Y2,V2,K2" +
                        " FROM ExcessDataView" +
                        " where 1=1 " + condition +
                        " ) as s " +
                        " where rowNumber>" + (limit * (page - 1)).ToString();

                List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
                list = dao.GetList(sql, new string[] { "DeviceId", "Name", "Address", "DataTime", "Y1", "V1", "K1", "Y2", "V2", "K2" });
                SqlNum = "select count(*) as TotalNum FROM ExcessDataView where 1=1 " + condition;
                List<Dictionary<string, object>> ListNum = new List<Dictionary<string, object>>();
                ListNum = dao.GetList(SqlNum, new string[] { "TotalNum" });

                TableData td = new TableData();
                td.code = "";
                td.msg = "";
                td.count = int.Parse(ListNum[0]["TotalNum"].ToString());
                td.data = list;
                return Json(td);
            }
            catch (Exception ex)
            {
                Dictionary<string, object> datalog = new Dictionary<string, object>();
                datalog.Add("type", int.Parse("1"));
                datalog.Add("OpTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                datalog.Add("OpID", int.Parse("1"));
                datalog.Add("OpLog", "异常：加载需求管理表格异常 /PanelController/ExcessTableRequest:" + ex.Message.ToString().Replace("'", ""));
                dao.save("T_LOG", datalog);
                return Json('0');
            }

        }

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////
///主页面板

        public ActionResult MainView()
        {
            string Sql, Sql2;
            Sql = "select count(*) as TotalNum from t_o_dataTemp";
            Sql2 = " select count(*) as TotalNum from t_o_dataTemp where DataTime>DATEADD (hour,-1,GETDATE())";

            var ListNum = dao.GetList(Sql, new string[] { "TotalNum" });
            int SumNum = int.Parse(ListNum[0]["TotalNum"].ToString());

            ListNum = dao.GetList(Sql2, new string[] { "TotalNum" });
            int OnLineNum = int.Parse(ListNum[0]["TotalNum"].ToString());

            Sql = "select count(MN) as TotalNum from t_o_dataTemp where (Y1>1 or V1>15 or Y2>1 or V2>15)";
            ListNum = dao.GetList(Sql, new string[] { "TotalNum" });
            int ExcessNum = int.Parse(ListNum[0]["TotalNum"].ToString());

            ViewBag.SumNum = SumNum;
            ViewBag.OnLineNum = OnLineNum;
            ViewBag.OffLineNum = SumNum - OnLineNum;
            ViewBag.ExcessNum = ExcessNum;

            Sql = "select a.DeviceId,a.MN,a.Name,a.Address,a.ChannelNum,a.lon,a.lat,b.Y1,b.V1,b.K1,b.Y2,b.V2,b.K2,CONVERT(varchar,b.DataTime,120) as LastTime from t_d_Device a left join t_o_dataTemp b on a.mn=b.mn " +
                " where a.IsDelete=0";
            List<Dictionary<string, object>> DeviceData = new List<Dictionary<string, object>>();
            DeviceData = dao.GetList(Sql, new string[] { "DeviceId", "MN", "Name", "Address", "ChannelNum", "lon", "lat", "Y1", "V1", "K1", "Y2", "V2", "K2", "LastTime" });
            ViewBag.DeviceDataJson = Newtonsoft.Json.JsonConvert.SerializeObject(DeviceData);

            return View("Main");
        }

        public ActionResult GetBlowOffSUM()
        {
            string StartTime = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd HH:00:00");
            string EndTime = DateTime.Now.ToString("yyyy-MM-dd HH:00:00");
            Dictionary<string, object> Map = new Dictionary<string, object>();

            string sql = "BEGIN " +
                    " declare @startDate datetime" +
                    " declare @endDate datetime" +
                    " SELECT @startDate = '" + StartTime + "', @endDate = '" + EndTime + "'" +
                    " ; WITH tb AS(" +
                    " SELECT @startDate AS 'DateHour'" +
                    " UNION ALL" +
                    " SELECT DATEADD(HOUR, 1, DateHour) FROM tb WHERE DateHour < @endDate" +
                    " )" +
                    " SELECT DATEPART(HOUR, a.datehour) as DateHour, round(SUM(b.Y1+b.Y2), 2) as Y,round(SUM(b.V1+b.V2), 2) as V,round(SUM(b.K1+b.K2), 2) as K" +
                    " " +
                    " from tb a LEFT JOIN t_o_data b ON" +
                    " CONVERT(VARCHAR(100), a.DateHour, 112) = CONVERT(VARCHAR(100), b.DataTime, 112)" +
                    " AND DATEPART(HOUR, a.datehour)= DATEPART(hour, b.DataTime) AND b.Y1 > 0 " +
                    " GROUP BY datehour" +
                    " ORDER BY a.datehour ASC" +
                    " END";
            List<Dictionary<string, object>> ListData = new List<Dictionary<string, object>>();
            ListData = dao.GetList(sql, new string[] { "DateHour", "Y", "V", "K" });
            return Json(ListData);
        }


        public ActionResult GetBlowOffAvg()
        {

            string StartTime = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd HH:00:00");
            string EndTime = DateTime.Now.ToString("yyyy-MM-dd HH:00:00");
            Dictionary<string, object> Map = new Dictionary<string, object>();

            string sql = "BEGIN " +
                    " declare @startDate datetime" +
                    " declare @endDate datetime" +
                    " SELECT @startDate = '" + StartTime + "', @endDate = '" + EndTime + "'" +
                    " ; WITH tb AS(" +
                    " SELECT @startDate AS 'DateHour'" +
                    " UNION ALL" +
                    " SELECT DATEADD(HOUR, 1, DateHour) FROM tb WHERE DateHour < @endDate" +
                    " )" +
                    " SELECT DATEPART(HOUR, a.datehour) as DateHour, round(AVG(b.Y1), 2) as Y1,round(AVG(b.V1), 2) as V1,round(AVG(b.K1), 2) as K1," +
                    " round(AVG(b.Y2), 2) as Y2,round(AVG(b.V2), 2) as V2,round(AVG(b.K2), 2) as K2" +
                    " from tb a LEFT JOIN t_o_data b ON" +
                    " CONVERT(VARCHAR(100), a.DateHour, 112) = CONVERT(VARCHAR(100), b.DataTime, 112)" +
                    " AND DATEPART(HOUR, a.datehour)= DATEPART(hour, b.DataTime) AND b.Y1 > 0 " +
                    " GROUP BY datehour" +
                    " ORDER BY a.datehour ASC" +
                    " END";
            List<Dictionary<string, object>> ListData = new List<Dictionary<string, object>>();
            ListData = dao.GetList(sql, new string[] { "DateHour", "Y1", "V1", "K1", "Y2", "V2", "K2" });
            return Json(ListData);



        }


        public ActionResult GetOver_Y1()
        {
            string StartTime = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd HH:00:00");
            string EndTime = DateTime.Now.ToString("yyyy-MM-dd HH:00:00");
            string sql = "select A.MN,A.Name,t.Y1,t.Y1*100 as percentage,t.DataTime FROM t_d_Device A INNER JOIN";
            sql += "(";
            sql += "SELECT row_number() over (partition by mn order by Y1 DESC) AS KEYID, * FROM t_o_data where DataTime  BETWEEN '"+ StartTime + "' and '"+ EndTime + "' and Y1>1";
            sql += ") as t ON A.MN=T.MN where t.KEYID=1";
            List<Dictionary<string, object>> ListData = new List<Dictionary<string, object>>();
            ListData = dao.GetList(sql, new string[] { "MN", "Name", "Y1", "percentage", "DataTime" });
            return Json(ListData);


        }

    }
}